import sys
import os
sys.path.append(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
from dbutils.pooled_db import PooledDB
import pymysql
from config import *



class ReadFromMysql(object):
    def __init__(self, host=None, user=None,
                 password=None, port=None):
        self.host = host
        self.user = user
        self.password = password
        self.port = port
        self.pool = None

    def makepool(self, database):
        """
        :param database: 数据库
        :return: 相应库连接
        """
        pool = PooledDB(pymysql, 3, host=self.host,
                        user=self.user,
                        passwd=self.password,
                        db=database,
                        port=self.port,
                        setsession=['SET AUTOCOMMIT = 1'])
        if self.pool == None:
            self.pool = pool
        else:
            self.pool.close()
            self.pool = pool

    def getconn(self):
        conn = self.pool.connection()  # 以后每次需要数据库连接就是用connection（）函数获取连接就好了
        return conn

    def column_from_mysql(self, table_name):
        """
        :param database_name: 库名
        :param table_name: 表名
        :return: 表列名
        """
        conn = self.getconn()
        cs1 = conn.cursor()
        count2 = cs1.execute('SHOW FULL FIELDS FROM %s' % table_name)
        result2 = list(cs1.fetchall())
        columns = [column_set[0] for column_set in result2]
        cs1.close()
        conn.commit()
        conn.close()
        # print("查询到%d个特征:" % count2)
        return columns

    def read_from_mysql(self, sql):
        """
        :param sql: 获取数据sql
        :return: 返回数据
        """
        conn = self.getconn()
        cs = conn.cursor()
        cs.execute(sql)
        rs = cs.fetchall()
        desc = cs.description
        rslist = []
        for row in rs:
            one = {}
            for i in range(0, len(row)):
                one[desc[i][0]] = row[i]
            rslist.append(one)
        cs.close()
        conn.close()
        return rslist


if __name__ == '__main__':
    mysql = ReadFromMysql(host=mysqlhost, user=user, password=password, port=3306)
    mysql.makepool(database)
    conn = mysql.getconn()
    sc = conn.cursor()
    # column = mysql.column_from_mysql(tablename)
    # sc.execute(f"select * from {tablename}")
    # conn.commit()
    # result = sc.fetchall()
    # mappings = {}
    # dc = [dict(zip(column,i)) for i in result]
    # for i in dc:
    #     model = i.get("model", "general")
    #     category = i.get('category', "general")
    #     original_field = i.get('original_field')
    #     natrual_field = i.get('natrual_field')
    #     if not mappings.get(model):
    #         mappings[model] = {}
    #     if not mappings[model].get(category):
    #         mappings[model][category] = {}
    #     mappings[model][category][original_field] = natrual_field
    # print(mappings)




